1. Creating a date table.
DAX syntax needed for this step.
Date =
ADDCOLUMNS(
CALENDAR( MIN(vw_QS_MetricsDashboard_ClassManagement[Class_SessionDate]),
MAX( vw_QS_MetricsDashboard_ClassManagement[Class_SessionDate])
),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"Week Number", WEEKNUM ( [Date] ),
"Week Start Date" , [Date]-WEEKDAY([Date],1)+1,
"Week end" ,[Date] + 7 - WEEKDAY([Date],1),
"For the Week",FORMAT([Date]-WEEKDAY([Date],1)+1,"m/d") & "-" & FORMAT([Date] + 7 - WEEKDAY([Date],1),"m/d"),
"Week Number and Year", "W" & WEEKNUM ( [Date] ) & " " & YEAR ( [Date] ),
"WeekYearNumber", YEAR ( [Date] ) & 100 + WEEKNUM ( [Date] ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
GIF 1
Create a date table and enter the DAX query from above.
GIF 2
Go to transform data and change the Class_SessionDate data type to date.
2. Create Refresh table